﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using EIP.Common.Core.Extensions;
using EIP.Common.Core.Log;
using EIP.Common.Entities.CustomAttributes;

namespace EIP.Common.Dapper
{
    /// <summary>
    /// SqlMapper帮助类
    /// </summary>
    public static class SqlMapperUtil
    {
        public static string ConnectionStr = string.Empty;

        /// <summary>
        ///     获取数据库连接字符串并打开数据库连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetOpenConnection()
        {
            var connection = new SqlConnection(ConnectionStr);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return connection;
        }

        /// <summary>
        /// 获取对应数据库连接字符串
        /// </summary>
        /// <param name="entity"></param>
        public static void SetConnectionStr(object entity)
        {
            Type type = entity.GetType();
            var classAttr = type.GetCustomAttributes(typeof(DbAttribute), false);
            if (classAttr.Length > 0)
            {
                var info = classAttr[0] as DbAttribute;
                if (info != null)
                    ConnectionStr = ConfigurationManager.AppSettings[info.Name];
                if (string.IsNullOrEmpty(ConnectionStr))
                {
                    ConnectionStr = ConfigurationManager.AppSettings["ConnectionStrings"];
                }
            }
            else
            {
                ConnectionStr = ConfigurationManager.AppSettings["ConnectionStrings"];
            }
        }

        /// <summary>
        /// 获取对应数据库连接字符串
        /// </summary>
        /// <param name="type"></param>
        public static void SetConnectionStr(Type type)
        {
            var classAttr = type.GetCustomAttributes(typeof(DbAttribute), false);
            if (classAttr.Length > 0)
            {
                var info = classAttr[0] as DbAttribute;
                if (info != null)
                    ConnectionStr = ConfigurationManager.AppSettings[info.Name];
                if (string.IsNullOrEmpty(ConnectionStr))
                {
                    ConnectionStr = ConfigurationManager.AppSettings["ConnectionStrings"];
                }
            }
            else
            {
                ConnectionStr = ConfigurationManager.AppSettings["ConnectionStrings"];
            }
        }

        /// <summary>
        /// 写入SqlLog日志
        /// </summary>
        /// <param name="log"></param>
        public static void WriteSqlLog(SqlLog log)
        {
            SqlLogHandler handler = new SqlLogHandler(log.OperateSql, log.EndDateTime, log.ElapsedTime, log.Parameter);
            handler.WriteLog();
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="entities"></param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static Task<int> InsertMultiple<T>(string sql, IEnumerable<T> entities, bool isSetConnectionStr = true)
            where T : class, new()
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var cnn = GetOpenConnection())
            {
                int records;
                using (var trans = cnn.BeginTransaction())
                {
                    try
                    {
                        SqlLog log = new SqlLog
                        {
                            CreateTime = DateTime.Now,
                            Parameter = JsonExtension.ListToJsonString(entities),
                            OperateSql = sql
                        };
                        Stopwatch stopwatch = new Stopwatch();
                        stopwatch.Start();
                        records = cnn.Execute(sql, entities, trans, 30, CommandType.Text);
                        stopwatch.Stop();
                        log.EndDateTime = DateTime.Now;
                        log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                        WriteSqlLog(log);
                    }
                    catch (DataException)
                    {
                        trans.Rollback();
                        throw;
                    }
                    trans.Commit();
                }
                return Task.Factory.StartNew(() => records);
            }
        }
        /// <summary>
        /// 将List集合转换为DataTable
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IList<T> list)
        {
            var props = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();
            for (var i = 0; i < props.Count; i++)
            {
                var prop = props[i];
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }
            var values = new object[props.Count];
            foreach (var item in list)
            {
                for (var i = 0; i < values.Length; i++)
                    values[i] = props[i].GetValue(item) ?? DBNull.Value;
                table.Rows.Add(values);
            }
            return table;
        }

        public static DynamicParameters GetParametersFromObject(object obj, string[] propertyNamesToIgnore)
        {
            if (propertyNamesToIgnore == null) propertyNamesToIgnore = new[] { String.Empty };
            var p = new DynamicParameters();
            var properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (var prop in properties)
            {
                if (!propertyNamesToIgnore.Contains(prop.Name))
                    p.Add("@" + prop.Name, prop.GetValue(obj, null));
            }
            return p;
        }

        public static void SetIdentity<T>(IDbConnection connection, Action<T> setId)
        {
            dynamic identity = connection.Query("SELECT @@IDENTITY AS Id").Single();
            var newId = (T)identity.Id;
            setId(newId);
        }

        /// <summary>
        ///     Stored proc.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procname">The procname.</param>
        /// <param name="parms">The parms.</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static Task<IEnumerable<T>> StoredProcWithParams<T>(string procname, dynamic parms, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                   {
                       CreateTime = DateTime.Now,
                       Parameter = parms == null ? "" : parms.ToString(),
                       OperateSql = procname
                   };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query<T>(procname, (object)parms, commandType: CommandType.StoredProcedure);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     Stored proc.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procname">The procname.</param>
        /// <param name="parms">The parms.</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static IEnumerable<T> StoredProcWithParamsSync<T>(string procname, dynamic parms, bool isSetConnectionStr = true)
        {

            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = procname
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query<T>(procname, (object)parms, commandType: CommandType.StoredProcedure);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return result;
            }
        }

        /// <summary>
        ///     Stored proc with params returning dynamic.
        /// </summary>
        /// <param name="procname">The procname.</param>
        /// <param name="parms">The parms.</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> StoredProcWithParamsDynamic(string procname, dynamic parms)
        {
            using (var connection = GetOpenConnection())
            {
                return connection.Query(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList();
            }
        }

        /// <summary>
        ///     Stored proc insert with ID.
        /// </summary>
        /// <typeparam name="TU">The Type of the ID</typeparam>
        /// <param name="procName">Name of the proc.</param>
        /// <param name="parms">instance of DynamicParameters class. This should include a defined output parameter</param>
        /// <returns>U - the @@Identity value from output parameter</returns>
        public static TU StoredProcInsertWithId<TU>(string procName, DynamicParameters parms)
        {
            using (GetOpenConnection())
            {
                return parms.Get<TU>("@ID");
            }
        }

        /// <summary>
        ///     Sql查询
        /// </summary>
        /// <typeparam name="T">查询后映射实体</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static Task<IEnumerable<T>> SqlWithParams<T>(string sql, dynamic parms, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query<T>(sql, (object)parms);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     Sql查询
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static Task<bool> SqlWithParamsBool<T>(string sql, dynamic parms, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query(sql, (object)parms).Any();
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     Sql查询
        /// </summary>
        /// <typeparam name="T">查询后映射实体</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static Task<IEnumerable<T>> SqlWithParams<T>(string sql,
            bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = "",
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query<T>(sql);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     Sql查询
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static Task<IEnumerable<T>> SqlWithParamsDapper<T>(string sql,
            dynamic parms = null,
            bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));

            SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query<T>(sql, (object)parms);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     执行增加删除修改语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数信息</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns>影响数</returns>
        public static Task<int> InsertUpdateOrDeleteSql<T>(string sql, dynamic parms = null, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Execute(sql, (object)parms);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     执行增加删除修改语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数信息</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns>影响数</returns>
        public static Task<int> InsertUpdateOrDeleteExecuteScalarSql<T>(string sql, dynamic parms = null, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.ExecuteScalar<int>(sql, (object)parms);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     执行增加删除修改语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数信息</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns>影响数</returns>
        public static Task<bool> InsertUpdateOrDeleteSqlBool<T>(string sql, dynamic parms = null, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Execute(sql, (object)parms) > 0;
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     存储过程增加删除修改
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parms">参数</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns>影响条数</returns>
        public static Task<int> InsertUpdateOrDeleteStoredProc<T>(string procName, dynamic parms = null, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = procName
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure);
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        /// <summary>
        ///     根据Sql语句查询符合条件第一个
        /// </summary>
        /// <typeparam name="T">返回实体</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="parms">参数</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns>实体信息</returns>
        public static Task<T> SqlWithParamsSingle<T>(string sql, dynamic parms = null, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                SqlLog log = new SqlLog
                {
                    CreateTime = DateTime.Now,
                    Parameter = parms == null ? "" : parms.ToString(),
                    OperateSql = sql
                };
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                var result = connection.Query<T>(sql, (object)parms).FirstOrDefault();
                stopwatch.Stop();
                log.EndDateTime = DateTime.Now;
                log.ElapsedTime = stopwatch.Elapsed.TotalSeconds;
                WriteSqlLog(log);
                return Task.Factory.StartNew(() => result);
            }
        }

        ///// <summary>
        /////     根据存储过程查询符合条件第一个
        ///// </summary>
        ///// <param name="sql">Sql语句</param>
        ///// <param name="parms">参数</param>
        ///// <returns></returns>
        //public static Task<DynamicObject> DynamicProcWithParamsSingle(string sql, dynamic parms = null)
        //{
        //    using (var connection = GetOpenConnection())
        //    {
        //        Stopwatch stopwatch = new Stopwatch();
        //        stopwatch.Start();
        //        StringBuilder stringBuilder = new StringBuilder(string.Format("查询一个语句:{0}</br>参数:{1}", sql, (object)parms));
        //        var result = connection.Query(sql, (object)parms, commandType: CommandType.StoredProcedure).FirstOrDefault();
        //        stopwatch.Stop();
        //        stringBuilder.Append(string.Format("</br>结束时间:{0}</br>总耗时:{1}【毫秒】", DateTime.Now, stopwatch.Elapsed.TotalSeconds)); //这里是输出的总运行秒数,精确到毫秒的
        //        LogWriter.WriteLog(FolderName.SqlDoLog, stringBuilder.ToString());
        //        return Task.Factory.StartNew(() => result);
        //    }
        //}

        /// <summary>
        ///     带参数存储过程
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parms">The parms.</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> DynamicProcWithParams(string sql, dynamic parms = null)
        {
            using (var connection = GetOpenConnection())
            {
                return connection.Query(sql, (object)parms, commandType: CommandType.StoredProcedure);
            }
        }

        /// <summary>
        ///     Stored proc with params returning single.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procname">The procname.</param>
        /// <param name="parms">The parms.</param>
        /// <param name="isSetConnectionStr">是否需要重置连接字符串</param>
        /// <returns></returns>
        public static T StoredProcWithParamsSingle<T>(string procname, dynamic parms = null, bool isSetConnectionStr = true)
        {
            if (isSetConnectionStr)
                SetConnectionStr(typeof(T));
            using (var connection = GetOpenConnection())
            {
                return
                    connection.Query<T>(procname, (object)parms, commandType: CommandType.StoredProcedure)
                        .SingleOrDefault();
            }
        }
    }
}